package com.cn.wanix.dao.impl;

import com.cn.wanix.dao.UserDao;
import com.cn.wanix.model.UserModel;
import com.cn.wanix.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 數據实现层
 * 1.封装sql语句
 * 2.封装返回值
 * 3.手写一个jdbc代码，jdbc的步骤
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel login(UserModel userModel) {
        String sql = "select * from user where username=? and password =?";
//        JDBC工具类
        List<Object> list = new ArrayList<>();
        list.add(userModel.getUsername());
        list.add(userModel.getPassword());
        ResultSet query = JDBC.query(sql, list);
        UserModel model = null;
        try {
            while (query.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int updateEnable(UserModel userModel) {
//        String sqlll = "select * from user where id=" + userModel.getId();
//
//        UserModel model = setData(sqlll, null).get(0);
//        String sql = "";
//        if (model.getEnable() == 1) {
//            sql = "update user set enable=0 where id=" + userModel.getId();
//        } else {
//            sql = "update user set enable=1 where id=" + userModel.getId();
//        }
        String sql = "update user set enable = case enable when 1 then 0 when 0 then 1 else 1 end where id=" + userModel.getId();
        List<Object> list = new ArrayList<>();
        return JDBC.update(sql, list);
    }

    public int add(UserModel model) {
        String sql = "INSERT INTO `user` (`username`) VALUES (?);\n";
        List<Object> list = new ArrayList<>();
        list.add(model.getUsername());
        return JDBC.update(sql, list);
    }

    @Override
    public int update(UserModel model) {
        List<Object> list = new ArrayList<>();
        String sql = "UPDATE `user` SET ";
        if (model.getAddress() != null) {
            sql += "`address` = ?, ";
            list.add(model.getAddress());
        }
        if (model.getEmail() != null) {
            sql += "`email` = ?, ";
            list.add(model.getEmail());
        }
        sql += "`enable` =?," +
//                " `phone` = ?, " +
                "`sex` = ?, " +
                "`update_time` = now(), " +
                "`username` = ?," +
                " `hobby` = ?," +
                " `birthday` = ? " +
                "WHERE (`id` = ?);\n";
        list.add(model.getEnable());
        list.add(model.getSex());
        list.add(model.getUsername());
        list.add(model.getHobby());
        list.add(model.getBirthday());
        list.add(model.getId());
        return JDBC.update(sql, list);
    }

    @Override
    public int delete(UserModel model) {
        String sql = "delete from user where id=?";
        List<Object> list = new ArrayList<>();
        list.add(model.getId());
        return JDBC.update(sql, list);
    }

    @Override
    public List<UserModel> findAll(UserModel model) {
        StringBuilder sql = new StringBuilder("select * from user where 1=1 ");
        if (!"".equals(model.getUsername())) {
            sql.append(" and username like '%").append(model.getUsername()).append("%' ");
        }
        if (!"".equals(model.getEmail())) {
            sql.append(" and email like '%").append(model.getEmail()).append("%'");
        }
// 生日开始时间和生日结束时间
        if (!"".equals(model.getStart()) && model.getEnd().equals("")) {
            sql.append(" and birthday >='").append(model.getStart()).append("'");
        }
        if (model.getStart().equals("") && !model.getEnd().equals("")) {
            sql.append(" and birthday <='").append(model.getEnd()).append("'");
        }
        if (!model.getStart().equals("") && !model.getEnd().equals("")) {
            sql.append(" and birthday  between '").append(model.getStart()).append("' and '").append(model.getEnd()).append("'");
        }

        if (model.getEnable() != -1) {
            sql.append(" and enable=").append(model.getEnable());
        }
        if (!"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
//            这里展示的是and连接，如果用or连接怎么做？
            for (String hobby : hobbys
            ) {
                sql.append(" and find_in_set('").append(hobby).append("',hobby)");
            }

        }
        sql.append(" limit ").append((model.getPage() - 1) * model.getLimit()).append("  ,").append(model.getLimit());
        return setData(sql.toString(), null);
    }


    @Override
    public UserModel findByID(UserModel model) {
        String sql = "select * from user where id=?";
        List<Object> list = new ArrayList<>();
        list.add(model.getId());
        List<UserModel> userModelList = setData(sql, list);
        int length = userModelList.size();
        return length > 0 ? userModelList.get(0) : null;
    }

    private List<UserModel> setData(String sql, List<Object> data) {
        ResultSet query = JDBC.query(sql, data);
        List<UserModel> list = new ArrayList<>();
        try {
            while (query.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(query.getInt("id"));
                userModel.setUsername(query.getString("username"));
                userModel.setAddress(query.getString("address"));
                userModel.setCreateTime(query.getString("create_time"));
                userModel.setEmail(query.getString("email"));
                userModel.setEnable(query.getInt("enable"));
                userModel.setPhone(query.getString("phone"));
                userModel.setSex(query.getString("sex"));
                userModel.setUpdateTime(query.getString("update_time"));
                userModel.setHobby(query.getString("hobby"));
                userModel.setBirthday(query.getString("birthday"));
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(UserModel model) {
        StringBuilder sql = new StringBuilder("select count(*) count from user where 1=1 ");
        if (!"".equals(model.getUsername())) {
            sql.append(" and username like '%").append(model.getUsername()).append("%' ");
        }
        if (!"".equals(model.getEmail())) {
            sql.append(" and email like '%").append(model.getEmail()).append("%'");
        }
// 生日开始时间和生日结束时间
        if (!"".equals(model.getStart()) && model.getEnd().equals("")) {
            sql.append(" and birthday >='").append(model.getStart()).append("'");
        }
        if (model.getStart().equals("") && !model.getEnd().equals("")) {
            sql.append(" and birthday <='").append(model.getEnd()).append("'");
        }
        if (!model.getStart().equals("") && !model.getEnd().equals("")) {
            sql.append(" and birthday  between '").append(model.getStart()).append("' and '").append(model.getEnd()).append("'");
        }

        if (model.getEnable() != -1) {
            sql.append(" and enable=").append(model.getEnable());
        }
        if (!"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
//            这里展示的是and连接，如果用or连接怎么做？
            for (String hobby : hobbys
            ) {
                sql.append(" and find_in_set('").append(hobby).append("',hobby)");
            }

        }
        ResultSet query = JDBC.query(sql.toString(), null);
        try {
            while (query.next()) {
                return query.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
